package com.ar4j.bench.jdbc.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;

import com.ar4j.bench.common.domain.IItem;
import com.ar4j.bench.common.service.IItemService;
import com.ar4j.bench.jdbc.domain.Item;

/**
 * An item service implemented using spring's JdbcTemplate
 */
@Service
public class ItemJdbcService implements InitializingBean, IItemService {
  private static final String ITEM_BY_ID = "SELECT ID, PRODUCT_ID, LIST_PRICE, UNIT_COST, NAME, IMAGE, DESCRIPTION FROM ITEM WHERE ID = ?";
  private static final String ITEMS_BY_PRODUCT_ID = "SELECT ID, PRODUCT_ID, LIST_PRICE, UNIT_COST, NAME, IMAGE, DESCRIPTION FROM ITEM WHERE PRODUCT_ID = ?";
  private static final String DELETE_BY_ID = "DELETE FROM ITEM WHERE ID = ?";
  
  private static final ItemMapper MAPPER = new ItemMapper();
  
  @Autowired
  private DataSource dataSource;
  
  private JdbcTemplate template;

  public Long addItem(IItem item) {
    KeyHolder holder = new GeneratedKeyHolder();
    template.update(new ItemInsert(item), holder);
    return holder.getKey().longValue();
  }
  
  public IItem findById(Long id) {
    List<IItem> results = template.query(ITEM_BY_ID, new Object[] { id }, MAPPER);
    if(results.size() != 1) {
      throw new IncorrectResultSizeDataAccessException(1, results.size());
    }
    
    return results.get(0);
  }

  public List<IItem> findByProductId(Long productId) {
    return template.query(ITEMS_BY_PRODUCT_ID, new Object[] { productId }, MAPPER);
  }

  public void updateItem(IItem item) {
    template.update(new ItemUpdate(item));
  }

  public void removeById(Long id) {
    template.update(DELETE_BY_ID, id);
  }

  @Override
  public void afterPropertiesSet() throws Exception {
    template = new JdbcTemplate(dataSource);
  }

  private static class ItemInsert implements PreparedStatementCreator {
    private static final String SQL = "INSERT INTO ITEM(PRODUCT_ID, LIST_PRICE, UNIT_COST, NAME, IMAGE, DESCRIPTION) VALUE (?, ?, ?, ?, ?, ?)";
    
    private IItem item;
    
    public ItemInsert(IItem item) {
      this.item = item;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      PreparedStatement ps = connection.prepareStatement(SQL, Statement.RETURN_GENERATED_KEYS);
      
      int nextParameter = 1;
      ps.setLong(nextParameter++, item.getProductId());
      ps.setDouble(nextParameter++, item.getListPrice());
      ps.setDouble(nextParameter++, item.getUnitCost());
      ps.setString(nextParameter++, item.getName());
      ps.setString(nextParameter++, item.getImage());
      ps.setString(nextParameter++, item.getDescription());
      
      return ps;
    }
  }

  public static class ItemMapper implements RowMapper<IItem> {

    @Override
    public IItem mapRow(ResultSet rs, int rowNum) throws SQLException {
      IItem out = new Item();
      
      int nextParameter = 1;
      out.setId(rs.getLong(nextParameter++));
      out.setProductId(rs.getLong(nextParameter++));
      out.setListPrice(rs.getDouble(nextParameter++));
      out.setUnitCost(rs.getDouble(nextParameter++));
      out.setName(rs.getString(nextParameter++));
      out.setImage(rs.getString(nextParameter++));
      out.setDescription(rs.getString(nextParameter++));
      
      return out;
    }
  }

  private static class ItemUpdate implements PreparedStatementCreator {
    private static final String SQL = "UPDATE ITEM SET PRODUCT_ID = ?, LIST_PRICE = ?, UNIT_COST = ?, NAME = ?, IMAGE = ?, DESCRIPTION = ? WHERE ID = ?";
    
    private IItem item;
    
    public ItemUpdate(IItem item) {
      this.item = item;
    }

    @Override
    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
      PreparedStatement ps = connection.prepareStatement(SQL);
      
      int nextParameter = 1;
      ps.setLong(nextParameter++, item.getProductId());
      ps.setDouble(nextParameter++, item.getListPrice());
      ps.setDouble(nextParameter++, item.getUnitCost());
      ps.setString(nextParameter++, item.getName());
      ps.setString(nextParameter++, item.getImage());
      ps.setString(nextParameter++, item.getDescription());
      ps.setLong(nextParameter++, item.getId());
      
      return ps;
    }
  }
}
